vincentwsxufandomcom-20200215-history
Convert the csv to a sql script
!/bin/ksh # RUN_TIME = date + "%d%m%y %H%M%S" RUN_DATE = date + "%d%m%y" echo " =" echo " Running Script : ` basename $0 ` echo " =" umask 0 PROG=` basename $0` USERNAME = echo $HOME | awk `{ FS-"/";print $2}` CURRENT_DIR = pwd DEFAULT_DATABASE_NAME = "$FDA_DATABASE" DEFAULT_SERVER_NAME = "$DSQUERY" DEFAULT_BASEDIR = "${HOME}/data_scripts" DEFAULT_INPUT_DIR = "${DEFAULT_BASEDIR}" DEFAULT_OUT_DIR="." DEFAULT_DATA_DIR="${DEFAULT_BASEDIR}" DEFAULT_DDL_DIR="${DEFAULT_BASEDIR}" DEFAULT_SQL_EXENSION=".sql" DEFAULT_PASSWORD="$FDA_PASSWORD" DEFAULT_USER="$FDA_USERNAME" CFGDIR="${DEFAULT_BASEDIR}" CFGFLIE= RUN_DIR= pwd RUN_SCRIPT= `basename $0"" LINE_CHECK="LIV" BEGIN_COMMENT="/********************************************" END_COMMENT="********************************************/" COMPLETED_FLAG="n" User_Defined_Flag="n" # = # Establish Working DIR # = OUTPUT_DIR="." if [ -d "${DEFAULT_OUT_DIR}" ]; then OUTPUT_DIR = ${DEFAULT_OUTPUT_DIR} fi SHELL_EXT=${DEFAULT_SHELL_EXTENSION} SCRIPT_NAME = `basename $0` FILESTEM=`base $0 ${SHELL_EXT} | awk `{FS=".";print $1}` SQL_SCRIPT="$SQL_SCRIPT_DIR="${HOME}/bin" OUTPUT_LOG="${OUTPUT_DIR}/${FILESTEM}.log" TEMP_OUTPUT_FILE1="/tmp/${FILESTEM}.$$.tmp1" TEMP_OUTPUT_FILE2="/tmp/${FILESTEM}.$$.tmp2" TEMP_OUTPUT_FILE3="/tmp/${FILESTEM}.$$.tmp3" TEMP_INPUT_FILE1="/tmp/${FILESTEM}.$$.tmp4" cat /dev/null > ${TEMP_OUTPUT_FILE1} cat /dev/null > ${TEMP_OUTPUT_FILE2} cat /dev/null > ${TEMP_OUTPUT_FILE3} cat /dev/null > ${OUTPUT_LOG} if [ `ps -ef | grep -v $$ | grep -v $0` -gt 0 ; then echo "" exit 1 fi # # Remove temporary files # trap 'if [ -f "${TEMP_OUTPUT_FILE1}" ]; then rm -f ${TEMP_OUTPUT_FILE1}; fi; if [ -f "${TEMP_OUTPUT_FILE2}" ]; then rm -f ${TEMP_OUTPUT_FILE2}; fi; if [ -f "${TEMP_OUTPUT_FILE3}" ]; then rm -f ${TEMP_OUTPUT_FILE3 fi; if [ -f "${TEMP_INPUT_FILE}" ]; then rm -f ${TEMP_INPUT_FILE} fi; exit' 0 # # Prompt Yes/No # function prompt_for_yes_or_no { PROMPTED_ANSWER= while != "y" && \ != "n" ; do if [ "$PROMPTED_ANSWER" = "" ]; then echo "${PROMPTED_QUESTION} y/n... \n" else echo "Answer must be 'y' or 'n' y/n... \n" fi read PROMPTED_ANSWER if [ -z "${PROMPTED_ANSWER}" ]; then PROMPTED_ANSWER="y" fi PROMPTED_ANSWER=`echo ${PROMPTED_ANSWER}| tr 'A-Z' 'a-z' done } # # access_isql # function access_isql { Isql_Access_Result="y" if [ -x "${SYBASE}/${SYBASE_OCS}/bin/isql"]; then ISQL_PATH="${SYBASE}/${SYBASE_OCS}/bin" else if [ -x "${SYBASE}/bin/isql" ]; then ISQL_PATCH="${SYBASE}/bin" else echo "\n" >> ${OUTPUT_LOG} echo "Cannot find path to 'isql' untility:" >> ${OUTPUT_LOG} if [ -d "${SYBASE}/${SYBASE_OCS}" ]; then echo "${SYBASE}/${SYBASE_OCS}" >> ${OUTPUT_LOG} else if [ -d "${SYBASE}" ]; then echo "${SYBASE}" >> ${OUTPUT_LOG} else echo "Sybase variable ot set." >> ${OUTPUT_LOG" fi fi Isql_Access_Result="n" fi fi SYBPWD="NoPassword" if [ `echo #${DEFAULT_PASSWORD}" | grep -c A-Za-z` -gt 0]; then SYBPWD-${DEFAULT_PASSWORD} else if [ -s "${CFGDIR}"/"${CFGFILE}".pw] && \ [ -r "${CFGDIR}"/}${CFGFILE}".pw ]; then KEYWORD1="a-zA-Z" KEYWORD2="a-zA-Z" if [ `grep -c ${SERVER_NAME} ${CFGDIR}/${CFGFILE}.pw` -gt 0 ]; then KEYWORD1="${SERVER_NAME}" fi if [ `grep -c "${DEFAULT_USER} " ${CFGDIR}/${CFGFILE}.pw` -gt 0 ]; then KEYWORD2="${DEFAULT_USER}" if [ `grep "${DEFAULT_USER} " ${CFGDIR}/${CFGFILE}.pw | grep -c "${SERVER_NAME}" - eq 0]; then KEYWORD1="a-zA-Z" fi fi SYBPWD=`cat ${CFGDIR}/${CFGILE}.pw | grep ${KEYWORD1} | gre ${KEYWORD2}\ | sed "s/${SERVER_NAME}//q" if [ `echo "${SYSTEM}" | wc - w` -gt 1 ]; then if [ `echo "${SYBPWD}" | sed "s/${DEFAULT_USER}//g" | grep -c "A-Za-z" -eq 0]; then SYBPWD=`echo "${SYBPWD}" | awk `{print $1}' | sed "s //g"` else SYBPWD=`echo "${SYBPWD}" | sed "s/${DEFAULT_USER}//g" | sed "s/ //g" fi fi if [ -z "${SYBPWD}" ]; then SYBPWD= `cat ${CFGDIR}/"${CFGFILE}".pw | awk `{pirnt $1}` | head -l1 fi fi fi if [ "${Isql_Access_Result}" = "y"]; then echo ${SYBPWD}| \ ${ISQL_PATH}/isql -U${DEFAULT_USER} -S${SERVER_NAME} -w240 -i${TEMP_INPUT_FILE1} > ${TEMP_OUTPUT_FILE1} if [ `grep -c "ct_connect" "${TEMP_OUTPUT_FILE1}" -gt 0 ]; then echo "\n" echo "Requested server could not be accessed." > ${TEMP_OUTPUT_FILE2} echo "Server : ${SERVER_NAME}" >> ${TEMP_OUTPUT_FILE2} echo "User : ${DEFAULT_USER}" >> ${TEMP_OUTPUT_FILE2} echo "\n" >> ${TEMP_OUTPUT_FILE2} cat ${TEMP_OUTPUT_FILE1} >> ${TEMP_OUTPUT_FILE2} fi fi # # Main Process # cat /dev/null > ${OUTPUT_LOG} cat /dev/null > ${TEMP_OUTPUT_FILE1} STARTTIME = `date echo "${STARTTIME}" > "${OUTPUT_LOG}" if [ "$#" -lt 1 ]; then echo "Incorrect parameter usage" fi SEARCH_PATH="$1" SERVER_NAME="$2" DATABASE_NAME="$3" if [ `echo "${DATABASE_NAME}" | grep -c "A-Za-z" -eq 0 ]; then DATABASE_NAME="${DEFAULT_DATABASE_NAME]" fi if [ `echo "${SERVER_NAME}" | grep -c "A-Za-z" -eq 0]; then SERVER_NAME = "${DEFAULT_SERVER_NAME}" fi SERACH_DIR=dirname "${SEARCH_PATH}" INPUT_FILE=basename "${SERACH_PATH}" if [ "${SEARCHDIR}" = "."]; then SERACH_DIR = pwd fi if [ ! -f "${SEARCH_PATH}" ]; then echo "\nNo file found matching the description by parameter." | tee -a "${TEMP_OUTPUT_FILE2}" echo "(${INPUT+FIEL}\n" | tee -a "${TEMP_OUTPUT_FIEL2}" Exit 3 else echo "user ${DATABASE_NAME}" > "${SQL_SCRIPT}" echo "go\n">> "${SQL_SCRIPT}" cat "${SEARCH_PATH}" | grep -v -i "ao_tgrading_period"\ | sed "s/",//g" \ | grep "^A-Za-z0-9" > "${TEMP_OUTPUT_FILE3}" Tansaction_Counter="0" Row_Counter="0" if { `head -l "${SEARCH_PATH}" | grep -c -i "ao_trading_period" -gt 0]; then let Row_Counter="${Row_Counter}" + 1 fi cat "${TEMP_OUTPUT_FIEL3}" |\ while read CURRENT_LINE do let Row_Counter="${Row_Counter}"+1 SERVICE_ID=`echo ${CURRENT_LINE} | awk '{FS=","print $1}` MARKET_ID=`echo ${CURRENT_LINE} | awk '{FS=","print $2}` INSTRUCTION_SELECTOR=`echo ${CURRENT_LINE} | awk '{FS=","print $3}` ORDER_PRICE_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $4}` ORDER_PRICE_TYPE_QUALIFIER=`echo ${CURRENT_LINE} | awk '{FS=","print $5}` EXPIRY_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $6}` TRADING_PERIOD=`echo ${CURRENT_LINE} | awk '{FS=","print $7}` ROUTE_ID=`echo ${CURRENT_LINE} | awk '{FS=","print $8}` CONVERT_FLAG=`echo ${CURRENT_LINE} | awk '{FS=","print $9}` CONVERT_STOP_PRICE=`echo ${CURRENT_LINE} | awk '{FS=","print $10}` AO_PRICE_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $11}` AO_PRICE_TYPE_QUALIFIER=`echo ${CURRENT_LINE} | awk '{FS=","print $12}` AO_EXPIRY_TYPE=`echo ${CURRENT_LINE} | awk '{FS=","print $13}` AO_TRADING_PERIOD=`echo ${CURRENT_LINE} | awk '{FS=","print $14}` FIX_TAG40=`echo ${CURRENT_LINE} | awk '{FS=","print $15}` FIX_TAG59=`echo ${CURRENT_LINE} | awk '{FS=","print $16}` FIX_TAG18=`echo ${CURRENT_LINE} | awk '{FS=","print $17}` FIX_TAG21=`echo ${CURRENT_LINE} | awk '{FS=","print $18}` let Transaction_Counter="${Transaction_Counter}"+1 echo "Print \"Row: ${Row_Counter} Transaction: ${Transaction_Counter} \c" | tee -a "${TEMP_OUTPUT_FILE2}" echo "Service Id: ${SERVICE_ID} Market Id: ${MARKET_ID} Instruction Selector : ${INSTRUCTION_SELECTOR} \" | tee -a "${TEMP_OUTPUT_FILE2}" echo "go\n" >> "${TEMP_OUTPUT_FILE2}" echo "" >> "${TEMP_OUTPUT_FILE2}" echo "exec fda_dbm_xpo_ordtype_fix_map" >> "$S{TEMP_OUTPUT_FILE2}" echo " @iSC_operation =\"M\"," >> "${TEMP_OUTPUT_FILE2}" echo " @iSC_admin_user=\"SUPPORT\"," >> "${TEMP_OUTPUT_FILE2}" echo " @iSC_workstation=\"daiwa\"," >> "${TEMP_OUTPUT_FILE2}" echo " @service_id =\"${SERVICE_ID}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @market_id = \"${MARKET_ID}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @instruction_selector = \"${INSTRUCTION_SELECTOR}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @order_price_type = \"${ORDER_PRICE_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @order_price_type_qualifier = \"${ORDER_PRICE_TYPE_QUALIFIER}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @expiry_type = \"${EXPIRY_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @trading_period = \"${TRADING_PERIOD}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @route_id = \"${ROUTE_ID}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @convert_flag = \"${CONVERT_FLAG}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @convert_stop_price = \"${CONVERT_STOP_PRICE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_PRICE_TYPE = \"${AO_PRICE_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_PRICE_TYPE_QUALIFIER = \"${AO_PRICE_TYPE_QUALIFIER}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_EXPIRY_TYPE = \"${AO_EXPIRY_TYPE}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @AO_TRADING_PERIOD = \"${AO_TRADING_PERIOD}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag40 = \"${FIX_TAG40}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag59 = \"${FIX_TAG59}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag18 = \"${FIX_TAG18}\"," >> "${TEMP_OUTPUT_FILE2}" echo " @fix_tag21 = \"${FIX_TAG21}\"," >> "${TEMP_OUTPUT_FILE2}" done fi ENDTIME=`date` echo "${ENDTIME}\n" >> "${OUTPUT_LOG}" echo "/******************************************" >> "${OUTPUT_LOG}" echo "Server: ${SERVER_NAME}" >> "${OUTPUT_LOG}" echo "************************************* \n" >> "${OUTPUT_LOG}" echo "Database: ${DATABASE_NAME}" >> "${OUTPUT_LOG}" echo "********************************************/ \n\n" >> "${OUTPUT_LOG}" cat "${TEMP_OUTPUT_FILE2}" >> "${SQL_SCRIPT}" cat "${TEMP_OUTPUT_FILE2}" >> "${OUTPUT_LOG}" vi "${OUTPUT_LOG}" if [ -f "${TEMP_OUTPUT_FILE1}" ]; then rm -f ${TEMP_OUTPUT_FILE1} fi if [ -f "${TEMP_OUTPUT_FILE2}" ]; then rm -f ${TEMP_OUTPUT_FILE2} fi if [ -f "${TEMP_OUTPUT_FILE3}" ]; then rm -f ${TEMP_OUTPUT_FILE3} fi if [ -f "${TEMP_INPUT_FILE1}" ]; then rm -f ${TEMP_INPUT_FILE1} fi